﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.customer.dao.impl;

import java.util.*;

import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.customer.dao.*;
import com.hh.bss.customer.entity.*;
import com.hh.bss.customer.service.*;
import com.hh.bss.customer.vo.query.*;
import com.hh.bss.util.*;



import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class CompanyCvDaoImpl extends BaseHibernateDao<CompanyCv, java.lang.Integer> implements CompanyCvDao<CompanyCv, java.lang.Integer> {

	public Class getEntityClass() {
		return CompanyCv.class;
	}

	public Page findPage(CompanyCvQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { CompanyCvQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuilder sql2 = new StringBuilder("select t from CompanyCv t where 1=1 ");
		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = :id ");
		}
		if (isNotEmpty(query.getCompanyId())) {
			sql2.append(" and  t.companyId = :companyId ");
		}
		if (isNotEmpty(query.getDeptId())) {
			sql2.append(" and  t.deptId = :deptId ");
		}
		if (isNotEmpty(query.getTitleId())) {
			sql2.append(" and  t.titleId = :titleId ");
		}
		if (isNotEmpty(query.getCvId())) {
			sql2.append(" and  t.cvId = :cvId ");
		}
		if (isNotEmpty(query.getComment())) {
			sql2.append(" and  t.comment like :comment ");
			query.setComment("%" + query.getComment() + "%");

		}
		if (isNotEmpty(query.getCreator())) {
			sql2.append(" and  t.creator like :creator ");
			query.setCreator("%" + query.getCreator() + "%");

		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql2.append(" and  t.createDt >= :createDtBegin ");
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql2.append(" and  t.createDt <= :createDtEnd ");
		}
		if (isNotEmpty(query.getModifier())) {
			sql2.append(" and  t.modifier like :modifier ");
			query.setModifier("%" + query.getModifier() + "%");

		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql2.append(" order by :sortColumns ");
		}

		return pageQuery(sql2.toString(), query);
	}

	public Page findSearchProjectCompanyCvPage(CompanyCvQuery query) {
		// TODO Auto-generated method stub
		// StringBuilder sql = new
		// StringBuilder("select t from CompanyCv t where 1=1 ");

		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.project_id projectId,a.company_id companyId,b1.name companyName,a.cv_id cvId,b.cv_name cvName,b.mobile,b.tel phone,b.email,c.name deptName,d.name titleName,e.current_salary currentSalary,b.married,b.sex,b.function,b.level,b.degree,b.address,b.work_year workYear,b.expect_location expectLocation from (select distinct a0.*,a1.project_id from  company_cv a0,search_project_cv a1 where a0.cv_id=a0.cv_id) a ");
		sql.append(" left join  cv b on a.cv_id=b.id ");
		sql.append(" left join  company b1 on a.company_id=b1.id ");
		sql.append(" left join company_dept c on a.dept_id=c.id ");
		sql.append(" left join company_dept_title d on a.title_id=d.id ");
		sql.append(" left join cv_salary e on a.cv_id=e.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.id=f.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.id=h.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.id=i.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.cv_id=j.cv_id ");
		sql.append(" ) comcv ");
		List values = new ArrayList();

		sql.append(" where comcv.projectId=? ");
		values.add(query.getProjectId());
		if (isNotEmpty(query.getCompanyId())) {
			sql.append(" and comcv.companyId=? ");
			values.add(query.getCompanyId());
		}

		// 有cc
		if ("1".equals(query.getHasCc())) {
			sql.append(" and exists( select 1 from ( select a.ref_cvid from company_cc a where exists (select 1 from search_project_company b where a.company_id=b.company_id and b.project_id=? )) bb where comcv.cvId=bb.ref_cvid ) ");
			values.add(query.getProjectId());
		}
		if ("1".equals(query.getHasCvlv())) {
			sql.append(" and exists (select 1 from cv a where ( exists (select 1 from cv_education a1 where a.id=a1.cv_id) or  exists (select 1 from cv_certificate a2 where a.id=a2.cv_id) or  exists (select 1 from cv_trainning a3 where a.id=a3.cv_id) or  exists (select 1 from cv_work a4 where a.id=a4.cv_id)) and comcv.cvId=a.id)  ");
		}

		if (isNotEmpty(query.getCvName())) {
			sql.append(" and comcv.cvName like ? ");
			values.add("%" + query.getCvName() + "%");
		}
		if (isNotEmpty(query.getPhone())) {
			sql.append(" and ( comcv.phone like ?  or  comcv.mobile like ? ) ");
			values.add("%" + query.getPhone() + "%");
			values.add("%" + query.getPhone() + "%");
		}
		if (isNotEmpty(query.getEmail())) {
			sql.append(" and comcv.email like ? ");
			values.add("%" + query.getEmail() + "%");
		}

		// return super.getJdbcDao().createPage(query, values, mapper,
		// pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
		// return pageQuery(sql2.toString(),query);
	}

	public List findCvCompany(CompanyCvQuery query) {
		// TODO Auto-generated method stub

		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select b.cv_name cvName,c.abbr_name abbrName,d.name dept ,e.name title ,a.create_dt createDt  from company_cv a ");
		sql.append(" left join cv b on a.cv_id=b.id ");
		sql.append(" left join company c on a.company_id=c.id ");
		sql.append(" left join company_dept d on a.dept_id=d.id ");
		sql.append(" left join company_dept_title e on a.title_id=e.id ");
		sql.append(" where  a.cv_id=? ");

		if (isNotEmpty("2".equals(query.getType()))) {
			sql.append(" and a.company_id<>ifnull(b.company_id,0) ");
		}

		sql.append(" ) comcv ");
		List values = new ArrayList();
		values.add(query.getCvId());
		List resutl = super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		return resutl;
		// return super.getJdbcDao().createPage(sql.toString(),values.toArray(),
		// new MapObjectRowMapper(), query.getPageSize(),
		// query.getPageNumber());
	}

	public Page findCompanyCv(CompanyCvQuery query) {
		// TODO Auto-generated method stub
		// 1:BD联系人，2:合作决策人，3:职位联系人，4:收款联系人，5:成功候选人（录入offer后更新），6：普通候选人
		// 1BD联系人，2合作决策人，3职位联系人，4收款联系人，普通候选人和成功候选人降序排列，成功候选人最底。
		// 2020 BD联系人
		// 2021 合作决策人
		// 2022 职位联系人
		// 2023 收款联系人
		// 2024 成功候选人
		// 2025 普通候选人
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.id,b.cv_name cvName,b.id cvId,c.abbr_name abbrName,d.name deptName ,b.title titleName ,b.position,b.cv_name_en cvNameEn,b.mobile,b.tel phone,b.email,b.cv_fl_level cvFlLevel,b.married,b.sex,b.function,b.level,b.degree,b.address,b.work_year workYear,b.expect_location expectLocation,b.current_address currentAddress,b.cv_type cvType,b.cv_tag cvTag,a.create_dt createDt,a.modified_dt modifiedDt  ");
		sql.append(" ,case when b.cv_type = '2020' then '6'  ");
		sql.append("   when b.cv_type = '2021' then '5'  ");
		sql.append("   when b.cv_type = '2022' then '4'  ");
		sql.append("   when b.cv_type = '2023' then '3'  ");
		sql.append("   when b.cv_type = '2024' then '1'  ");
		sql.append("   when b.cv_type = '2025' then '2'  ");
		sql.append("   else '0'  ");
		sql.append("  end sortColumn  ");
		sql.append(" from company_cv a ");
		sql.append(" left join cv b on a.cv_id=b.id ");
		sql.append(" left join company c on a.company_id=c.id ");
		sql.append(" left join company_dept d on a.dept_id=d.id ");
		sql.append(" left join company_dept_title e on a.title_id=e.id ");
		sql.append(" where  a.company_id=? ");

		sql.append(" ) comcv ");
		List values = new ArrayList();
		values.add(query.getCompanyId());
		if (isNotEmpty(query.getSortColumns())) {
			sql.append(" order by comcv.").append(query.getSortColumns());
		} else {
			sql.append(" order by comcv.sortColumn desc ,ifnull(comcv.modifiedDt,comcv.createDt) desc ");
		}
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());

	}

	/**
	 * 获取负责人 BD客户-->BD人 签约客户--->客户经理
	 */
	public String getManagerByCompanyId(Integer companyId) {
		// 1.判断是否存在 customer表里面
		// 2.是否为签约客户(合同有效) -->负责经理 (合同)
		// 3.是否为过期签约客户(合同无效)--->负责经理 (合同)
		// 4.是否为BD客户 --->BD人
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.company_id companyId,b.bd_account,c.id,d.account  from customer a ");
		sql.append(" left join (select *  from (select * from ( select  a1.bd_account,a1.cust_id  from customer_bd a1 order by a1.expired_d desc) a2 group by a2.cust_id ) a  )  b  on a.id=b.cust_id ");
		sql.append(" left  join (select distinct * from ( select * from ( select a.id, a.cust_id from customer_contract a order by a.end_d desc ) t group by t.cust_id union all select distinct a2.contract_id, a2.cust_id from ( select * from ( select a.id, a.cust_id from customer_contract a order by a.end_d desc ) t group by t.cust_id ) a1, customer_contract_service_company a2 where a1.id = a2.contract_id ) t1 ) c on a.id = c.cust_id ");
		sql.append(" left  join (select a.id,group_concat(a.account) account ,a.contract_id   from customer_contract_staff a where a.role='BDM' group by a.contract_id ) d on c.id = d.contract_id ");
		sql.append(" ) tab where 1=1 ");
		sql.append(" and tab.companyId =").append(companyId);
		List list = this.getJdbcDao().findByQuery(sql.toString(), new MapObjectRowMapper());
		if (list != null && list.size() > 0) {
			// Integer contractId = list.get(0);
			Map m = (Map) list.get(0);
			Integer contractId = (Integer) m.get("id");
			if (contractId != null && contractId > 0) {
				return (String) m.get("account");
			} else {
				return (String) m.get("bd_account");
			}
		}
		return null;
	}

}
